# Copyright (c) 2024, json_wang and contributors
# For license information, please see license.txt

import frappe
from frappe.model.document import Document
from erpnext.accounts.utils import FiscalYearError, get_balance_on, get_currency_precision, get_fiscal_year
from frappe.utils import cint, cstr, flt, getdate, datetime, get_first_day, get_last_day, formatdate
from frappe.utils import getdate
from frappe.utils.data import cstr, nowdate
import pandas as pd
from erpnext.accounts.utils import get_balance_on
from frappe.utils import flt


class AC_CashFlow(Document):
	@frappe.whitelist()
	def get_data(self):
		doc = frappe.get_doc("AC_CashFlow_Config", self.config_version)
		if not doc:
			frappe.throw("没有找到当前公司的报表配置!")
		doc_dict = doc.as_dict()
		report_date = datetime.datetime.strptime(self.report_date, "%Y-%m-%d").date()
		from_date =  self.report_date[:4] + "-01-01"
		to_date = get_last_day(report_date).strftime("%Y-%m-%d")		
		# 读取凭证数据	
		query_sql = f"""
with gl_temp as
(
	SELECT
	gl.voucher_no,
	gl.name as gl_name,
	gl.account,
	gl.debit,
	gl.credit,
	a.account_type,gl.posting_date,gl.against ,gl.project,gl.remarks  
	from `tabGL Entry` gl inner join `tabAccount` a on gl.account = a.name
	where gl.voucher_no in
	(
		SELECT gl.voucher_no
		from `tabGL Entry` gl 
		inner join `tabAccount` a on gl.account = a.name 
		where posting_date >= '{from_date}' and posting_date <= '{to_date}'
		and gl.company = '{self.company}'
		and a.account_type in ('Cash','Bank')
	)
	and gl.docstatus = 1
	and gl.is_cancelled != 1
	order by voucher_no
) 

select 
t1.voucher_no,
t1.gl_name,
t1.account,	
t1.credit,
t1.debit,
((t1.credit - t1.debit)/t3.total2)*t3.total as total,
t1.account_type,
t1.posting_date,
t1.against ,
ifnull(t2.project,'') as project,
t1.remarks,
ifnull(t2.against2,'') as against2 from 
(
	select * from gl_temp where account_type not in ('Cash','Bank')
) t1
left JOIN 
(
	select voucher_no,max(project) as project,max(against) as against2 from gl_temp where account_type in ('Cash','Bank') group by voucher_no
) t2 on t1.voucher_no = t2.voucher_no
left join
(
	select	
	voucher_no,
	sum(case when account_type in ('Cash','Bank') then debit - credit else 0 end) as total,
	sum(case when account_type not in ('Cash','Bank') then credit - debit else 0 end) as total2	
	from gl_temp group by voucher_no
) t3 on t1.voucher_no = t3.voucher_no"""
		data_list = frappe.db.sql(query_sql,as_dict=True) 
		# print(type(data_list))
		# print(type(data_list[0]))
		# print(data_list)
		# 创建一个空的结果字典
		# result_dict = {}

		# 遍历字典列表
		# for d in data_list:
		# 	for key, value in d.items():
		# 		# 如果键已存在，则添加值到列表中
		# 		if key in result_dict:
		# 			result_dict[key].append(value)
		# 		else:
		# 			# 如果键不存在，则创建新列表
		# 			result_dict[key] = [value]
		
		# print(result_dict)		
		#df_gl = pd.DataFrame(result_dict)	
		df_gl = pd.json_normalize(data_list)	
		print(df_gl.head(5))		

		# result_dict2 = {}
		# 遍历字典列表
		# for d in doc_dict['items']:
		# 	for key, value in d.items():
		# 		# 如果键已存在，则添加值到列表中
		# 		if key in result_dict2:
		# 			result_dict2[key].append(value)
		# 		else:
		# 			# 如果键不存在，则创建新列表
		# 			result_dict2[key] = [value]
		# print(result_dict2)	
		# df_map = pd.DataFrame(result_dict2)	
		df_map = pd.json_normalize(doc_dict['items'])	
		print(df_map.head(5))
		result_df = pd.merge(df_gl, df_map,left_on="account",right_on="acct",how="left")		
		result_df["project"] = result_df["project"].fillna("")
		result_df["rpt_item"] = result_df["rpt_item"].fillna("未分类报表项")
		result_df["rate"] = result_df["rate"].fillna(1)		
		result_df["sort_id"] = result_df["sort_id"].fillna(9999)
		result_df["amount"] = result_df["total"] * result_df["rate"] 
		self.gls = []
		for index, row in result_df.iterrows():
			#print(row)
			self.append(
				"gls",
				{
					"rpt_item": row["rpt_item"],
					"voucher_no": row["voucher_no"],
					"gl_name": row["gl_name"],
					"account": row["account"],
					"debit": row["debit"],
					"credit": row["credit"],
					"account_type": row["account_type"],
					"posting_date": row["posting_date"],
					"against": row["against"],
					"project": row["project"],
					"remarks": row["remarks"],
					"against2": row["against2"],
					"amount": row["amount"],
					"sort_id": row["sort_id"]
				},
			)		
		self.refresh_time = datetime.datetime.now()
		self.childs = []
		self.save(ignore_permissions=True)

	@frappe.whitelist()
	def build_report(self):
		report_date = datetime.datetime.strptime(self.report_date, "%Y-%m-%d").date()
		last_year_to_date=datetime.date(report_date.year-1, 12, 31).strftime("%Y-%m-%d")	
		last_year_doc_name = f"{self.config_version}-{last_year_to_date}"	
		doc_dict = self.as_dict()
		this_year_df = pd.json_normalize(doc_dict["gls"])
		this_year_df["amount2"] = 0
		union_df = this_year_df		
		if frappe.db.exists("AC_CashFlow", last_year_doc_name,cache=True):			
			last_year_doc_dict = frappe.get_doc("AC_CashFlow", last_year_doc_name).as_dict()
			if len(last_year_doc_dict["gls"]) > 0:
				last_year_df = pd.json_normalize(last_year_doc_dict["gls"])
				last_year_df["amount2"] = last_year_df["amount"]
				last_year_df["amount"] = 0
				print(last_year_df.head(5))
				union_df = pd.concat([this_year_df, last_year_df], ignore_index=True)
		print(union_df.head())			
		summary_df = (
            union_df.groupby("rpt_item")
            .agg({"amount": "sum", "amount2": "sum", "sort_id": "max"})
            .reset_index()
            .sort_values(by="sort_id")
        )		
		self.childs = []
		for index, row in summary_df.iterrows():
			self.append(
                "childs",
                {
                    "rpt_item": row["rpt_item"],
                    "this_year": row["amount"],
                    "last_year": row["amount2"],
                    "sort_id": row["sort_id"],
                }
            )
		self.refresh_time = datetime.datetime.now()
		self.save(ignore_permissions=True)


